/********************************************/
// Creation History
// --------------------------------------------------------------
//	Company:			HTHK
//	NAME:				DaySpendSearch.java
//    Title   :
//	FUNCTION:
//	BY:					Beyes Xia
//	DATE:				2004-4-19 13:57
// --------------------------------------------------------------
/*********************************************/

package tss.dayspendreport;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.log4j.Logger;

import tss.common.DBServlet2;

import com.ffpc.struts.StrutsMessage;
import com.util.JdbcUtil;

public class DaySpendSearch extends DBServlet2 {
	private Logger logger = Logger.getLogger(DaySpendSearch.class);
	@Override
	protected String perform(HttpServletRequest request,
			HttpServletResponse response, StrutsMessage message) {

		String staffName = (request.getParameter("staff_name") == null) ? ""
				: request.getParameter("staff_name");
		String projectName = (request.getParameter("project_name") == null) ? ""
				: request.getParameter("project_name");
		String dateFrom = (request.getParameter("date_from") == null) ? ""
				: request.getParameter("date_from");
		String dateTo = (request.getParameter("date_to") == null) ? ""
				: request.getParameter("date_to");

		logger.debug("staffName: " + staffName);
		logger.debug("projectName: " + projectName);
		logger.debug("dateFrom: " + dateFrom);
		logger.debug("dateTo: " + dateTo);

		HttpSession session = request.getSession();
		session.setAttribute("s_date_from", dateFrom);
		session.setAttribute("s_date_to", dateTo);
		session.setAttribute("s_staff_name", staffName);
		session.setAttribute("s_project_name", projectName);

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		ArrayList spend = null;
		String sql = null;
		try {
			conn = this.getConnection();
			stmt = conn.createStatement();
			spend = new ArrayList();
			sql = "Select b.alias||' '||b.surname staff_name,a.staff_no,"
					+ "a.project_name,a.job_name,"
					+ "to_char(a.spend_date,'YYYY/MM/DD') spend_date,"
					+ "to_char(a.spend_date,'Day','nls_date_language=American') week,"
					+ "spend "
					+ "From tss.tss_v_project_report a,tss.tss_c_employee b "
					+ "Where a.staff_no = b.staff_no ";
			if (staffName.length() != 0)
				sql += "And a.staff_no = '" + staffName + "' ";
			if (projectName.length() != 0)
				sql += "And a.project_name = '" + projectName + "' ";
			if (dateFrom.length() != 0)
				sql += "And a.spend_date >= to_date('" + dateFrom
						+ "','YYYY-MM-DD') ";
			if (dateTo.length() != 0)
				sql += "And a.spend_date <= to_date('" + dateTo
						+ "','YYYY-MM-DD') ";
			sql += "Order By b.alias,a.spend_date,a.job_name";
			logger.debug("sql is =" + sql);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String[] arraySpend = new String[7];
				arraySpend[0] = rs.getString(1);
				arraySpend[1] = rs.getString(2);
				arraySpend[2] = rs.getString(3);
				arraySpend[3] = rs.getString(4);
				arraySpend[4] = rs.getString(5);
				arraySpend[5] = rs.getString(6);
				arraySpend[6] = rs.getString(7);

				spend.add(arraySpend);
			}
		} catch (Exception e) {
			logger.error(e,e);
			message.setMessage("System error.");
			return "tss/dayspendreport/DaySpendExport.jsp";
		} finally {
			JdbcUtil.release(stmt, rs);
		}
		logger.debug("sql:" + sql);
		logger.debug("spend AL:" + spend);

		if (!spend.isEmpty()) {
			request.setAttribute("spend", spend);
			return "tss/dayspendreport/DaySpendReport.jsp";
		} else {
			message.setMessage("No record, please input another criteria.");
			return "tss/dayspendreport/DaySpendExport.jsp";
		}
	}
}
